package be.kuleuven.peno3.mobiletoledo.server;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Vector;

import javax.ws.rs.GET;
import javax.ws.rs.Path;
import javax.ws.rs.Produces;
import javax.ws.rs.QueryParam;

import org.json.simple.JSONObject;

import com.google.gson.Gson;
import com.google.gson.JsonArray;
import com.google.gson.JsonObject;

@Path ("/QuestionHandler")
public class MultipleChoiceServer {

	protected DatabaseManager manager = DatabaseManager.getInstance();

	//FormParam (altijd bij post) "beginDate" moet zelfde naam zijn als bij client
	@GET
	@Path ("/getQuestions")
	@Produces ("application/json")
	public String getQuestions(@QueryParam("course_id") int course_id){
		String query = "SELECT * FROM Questions";
	    if (course_id!=0) query += " WHERE course_id='"+course_id+"'";
	    
		String result = queryForQuestions(query);
		manager.disconnect();
		return result;
	}
	
	@GET
	@Path ("/getQuestion")
	@Produces ("application/json")
	public String getQuestion(@QueryParam("id") int id){
		String query = "SELECT * FROM Questions WHERE id='"+id+"' LIMIT 1";
	    
		String result = queryForQuestions(query);
		manager.disconnect();
		return result;
	}
	
	@SuppressWarnings({ "unchecked" })
	@GET
	@Path ("/deleteQuestion")
	@Produces ("application/json")
	public String deleteQuestion(@QueryParam("id") int id){
		JSONObject result = new JSONObject();
		try {
			String query = "DELETE FROM Questions where id='"+id+"';";
			manager.update(query);
			
			manager.disconnect();
		} catch (SQLException e1) {
			result.put("result", "DELETE of credentials failed");
			e1.printStackTrace();
			return result.toString();
		}
		
		result.put("result", "Question successfully deleted");
		manager.disconnect();
		return result.toString();
	}
	
	@SuppressWarnings({ "unchecked", "static-access" })
	@GET
	@Path ("/addQuestion")
	@Produces ("application/json")
	public String addQuestion(@QueryParam("author") String author, @QueryParam("content") String content, @QueryParam("course_id") int course_id, @QueryParam("possible_answers") String possible_answers){
		JSONObject result = new JSONObject();
		try {
			String query = "INSERT INTO Questions(author, content, course_id, possible_answers) VALUES ('"+author+"','"+content+"','"+course_id+"','"+possible_answers+"')";
			manager.update(query);
			
			query = "SELECT id FROM Questions WHERE author='"+author+"' AND content='"+content+"' AND course_id='"+course_id+"' AND possible_answers='"+possible_answers+"' LIMIT 1";
			ResultSet r = manager.query(query);
			r.next();
			JsonObject answer = (JsonObject) new Gson().toJsonTree(manager.getColumnValues(r));
			int id=answer.get("id").getAsInt();
			
			for(int i=0;i<possible_answers.split("\\|\\|\\|").length;i++) {
				query = "INSERT INTO Answers(questionid, answerid, counter) VALUES ('"+id+"','"+i+"','0')";
				manager.update(query);
			}
			
			manager.disconnect();
		} catch (SQLException e1) {
			result.put("result", "INSERT of credentials failed");
			e1.printStackTrace();
			return result.toString();
		}
		
		result.put("result", "Question successfully added");
		manager.disconnect();
		return result.toString();
	}
	
	@SuppressWarnings("unchecked")
	@GET
	@Path ("/addAnswer")
	@Produces ("application/json")
	public String addAnswer(@QueryParam("answerid") int answerid, @QueryParam("questionid") int questionid){
		JSONObject result = new JSONObject();
		try {
			String query = "UPDATE Answers SET counter=counter+1 WHERE answerid='"+answerid+"' AND questionid='"+questionid+"'";
			
			manager.update(query);
			
			manager.disconnect();
		} catch (SQLException e1) {
			result.put("result", "Update of Answers failed");
			e1.printStackTrace();
			return result.toString();
		}
		
		result.put("result", "Answer successfully added");
		manager.disconnect();
		return result.toString();
	}
	
	@GET
	@Path ("/getAnswers")
	@Produces ("application/json")
	public String getAnswers(@QueryParam("questionid") int questionId){
		String query = "SELECT * from Answers WHERE questionid='"+questionId+"'";
		String result = queryForAnswers(query);
		manager.disconnect();
		return result;
	}
	
	@SuppressWarnings("static-access")
	private String queryForAnswers(String query) {
		JsonArray answers = new JsonArray();
		ResultSet rs = manager.query(query);
		Gson gson = new Gson();
		try {
			while(rs.next()) {
				JsonObject answer = (JsonObject) gson.toJsonTree(manager.getColumnValues(rs));
				
				answers.add(answer);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (NullPointerException ex) {
			ex.printStackTrace();
		}
		String asString = answers.toString();
		return asString;
	}


	@SuppressWarnings("static-access")
	private String queryForQuestions(String query) {
		JsonArray questions = new JsonArray();
		ResultSet rs = manager.query(query);
		Gson gson = new Gson();
		try {
			while(rs.next()) {
				JsonObject question = (JsonObject) gson.toJsonTree(manager.getColumnValues(rs));
				
				//get corresponding course from the database
				int course_id = question.get("course_id").getAsInt();
				query = "SELECT * FROM Courses WHERE id='" + course_id + "' limit 1";
				question.add("course", querySimpleTable(query).get(0));

				questions.add(question);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (NullPointerException ex) {
			ex.printStackTrace();
		}
		String asString = questions.toString();
		return asString;
	}
	
	
	@SuppressWarnings({ "static-access", "unchecked", "rawtypes" })
	private JsonArray querySimpleTable(String query) {
		Vector course = new Vector();
		ResultSet rs = manager.query(query);
		Gson gson = new Gson();
		try {
			while(rs.next()) {
				course.add(manager.getColumnValues(rs));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return (JsonArray) gson.toJsonTree(course);
	}

	
}
